package com.houseman.workFlow.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.houseman.workFlow.bean.WorkFlowBean;
import com.houseman.workFlow.bean.WorkFlowGraph;
import com.houseman.workFlow.dao.GraphDao;
import com.houseman.workFlow.dto.NextStepInfo;
import com.houseman.workFlow.utils.DBUtils;

public class GraphDaoImpl implements GraphDao{

	/**
	 * 查询流程图信息
	 * @return
	 * @throws Exception
	 */
	public WorkFlowGraph queryGraphById(int graphId) throws Exception{
		//获得连接
		Connection conn = DBUtils.geConnection();
		
		PreparedStatement ps = conn.prepareStatement("SELECT GRAPHID,NAME,FOLDERID,PROCLASS,APPROLE,CREATER,CREATEDATE,UPDATER,UPDATEDATE FROM WORKFLOWGRAPH WHERE GRAPHID=?");
		ps.setInt(1, graphId);
		
		ResultSet rs = ps.executeQuery();
		
		WorkFlowGraph wg = new WorkFlowGraph();
		while (rs.next()) {
			wg.setGraphId(rs.getInt("GRAPHID"));
			wg.setName(rs.getString("NAME"));
			wg.setFolderId(rs.getInt("FOLDERID"));
			wg.setProClass(rs.getString("PROCLASS"));
			wg.setAppRole(rs.getString("APPROLE"));
			wg.setCreater(rs.getString("CREATER"));
			wg.setCreateDate(rs.getString("CREATEDATE"));
			wg.setUpdater(rs.getString("UPDATER"));
			wg.setUpdateDate(rs.getString("UPDATEDATE"));
		}
		
		//关闭连接
		DBUtils.close(conn, ps, rs);
		return wg;
	}
	
	/**
	 * 查询流程图信息
	 * @return
	 * @throws Exception
	 */
	public List<WorkFlowGraph> queryGraphsByFolder() throws Exception{
		//获得连接
		Connection conn = DBUtils.geConnection();
		
		PreparedStatement ps = conn.prepareStatement("SELECT GRAPHID,NAME,FOLDERID FROM WORKFLOWGRAPH");
		
		ResultSet rs = ps.executeQuery();
		
		List<WorkFlowGraph> lgs = new ArrayList<WorkFlowGraph>();
		while (rs.next()) {
			WorkFlowGraph wg = new WorkFlowGraph();
			wg.setGraphId(rs.getInt("GRAPHID"));
			wg.setName(rs.getString("NAME"));
			wg.setFolderId(rs.getInt("FOLDERID"));
			
			lgs.add(wg);
		}
		
		//关闭连接
		DBUtils.close(conn, ps, rs);
		return lgs;
	}
	
	/**
	 * 根据名称和文件夹Id获得流程graphId
	 * @param graph
	 * @throws Exception
	 */
	public int getGraphId(String name, int folderId) throws Exception {
		
		//获得连接
		Connection conn = DBUtils.geConnection();
		
		int graphId = 0;
		PreparedStatement ps = conn.prepareStatement("SELECT GRAPHID FROM WORKFLOWGRAPH WHERE FOLDERID=? AND NAME=?");
		ps.setInt(1, folderId);
		ps.setString(2, name);
		ResultSet rs = ps.executeQuery();
		while (rs.next()) {
			graphId = rs.getInt("GRAPHID");
		}
		
		DBUtils.close(conn, ps, rs);
		
		return graphId;
	}
	
	/**
	 * 保存流程图信息
	 * @param graph
	 * @throws Exception
	 */
	public int savaGraph(WorkFlowGraph graph) throws Exception{
		//获得连接
		Connection conn = DBUtils.geConnection();
		
		//insert workFlowGraph
		PreparedStatement ps = conn.prepareStatement("INSERT INTO WORKFLOWGRAPH(NAME,FOLDERID,PROCLASS,APPROLE,CREATER,CREATEDATE) VALUES(?,?,?,?,?,NOW())", Statement.RETURN_GENERATED_KEYS);
		ps.setString(1, graph.getName());
		ps.setInt(2, graph.getFolderId());
		ps.setString(3, graph.getProClass());
		ps.setString(4, graph.getAppRole());
		ps.setString(5, graph.getCreater());
		ps.execute(); //新增 删除
		
		int graphId = 0;
		ResultSet rs = ps.getGeneratedKeys();
		while (rs.next()) {
			graphId = rs.getInt(1);
		}
		//关闭连接
		DBUtils.close(conn, ps, rs);
		return graphId;
	}
	
	/**
	 * 更新流程图信息
	 * @param graph
	 * @throws Exception
	 */
	public void updateGraph(WorkFlowGraph graph) throws Exception {
		//获得连接
		Connection conn = DBUtils.geConnection();
		
		PreparedStatement ps = conn.prepareStatement("UPDATE WORKFLOWGRAPH SET NAME=?,FOLDERID=?,PROCLASS=?,APPROLE=?,UPDATER=?,UPDATEDATE=NOW() WHERE GRAPHID=?");
		ps.setString(1, graph.getName());
		ps.setInt(2, graph.getFolderId());
		ps.setString(3, graph.getProClass());
		ps.setString(4, graph.getAppRole());
		ps.setString(5, graph.getUpdater());
		ps.setInt(6, graph.getGraphId());
		
		ps.executeUpdate();
		
		DBUtils.close(conn, ps);
	}
	
	/**
	 * 删除流程图
	 * @param GRAPHID
	 * @throws Exception
	 */
	public void deleteGraph(int graphId) throws Exception {
		//获得连接
		Connection conn = DBUtils.geConnection();
		
		PreparedStatement ps = conn.prepareStatement("DELETE FROM LINE WHERE GRAPHID=?");
		ps.setInt(1, graphId);
		
		ps.execute();
		
		DBUtils.close(conn, ps);
	}

	/**
	 * 根据用户角色查询其可以发起的流程列表
	 * @param role
	 * @return
	 * @throws Exception
	 */
	public List<WorkFlowGraph> queryStartWFByRole(String role) throws Exception {
		return null;
	}

	/**
	 * 查询流程下一代办的信息
	 * @param GRAPHID
	 * @param step
	 * @return
	 * @throws Exception
	 */
	public List<NextStepInfo> getNextStepInfo(int graphId, int step) throws Exception {
		//获得连接
		Connection conn = DBUtils.geConnection();
		
		PreparedStatement ps = conn.prepareStatement("SELECT GRAPHID,NAME,FOLDERID FROM WORKFLOWGRAPH");
		
		ResultSet rs = ps.executeQuery();
		
		List<NextStepInfo> infos = new ArrayList<NextStepInfo>();
		while (rs.next()) {
			NextStepInfo ns = new NextStepInfo();
			
			infos.add(ns);
		}
		
		//关闭连接
		DBUtils.close(conn, ps, rs);
		return infos;
	}

	/**
	 * 根据graphId查询graph信息
	 * @param graphId
	 * @return
	 * @throws Exception
	 */
	public WorkFlowBean queryNewBuildGraph(int graphId) throws Exception {
		//获得连接
		Connection conn = DBUtils.geConnection();
		
		PreparedStatement ps = conn.prepareStatement("SELECT W.GRAPHID,W.NAME,W.PROCLASS,T.STEP FROM TASKAREA T,WORKFLOWGRAPH W WHERE T.GRAPHID=W.GRAPHID AND T.FLAG=1 AND W.GRAPHID=?");
		ps.setInt(1, graphId);
		
		ResultSet rs = ps.executeQuery();
		
		WorkFlowBean wb = new WorkFlowBean();
		while (rs.next()) {
			wb.setGraphId(rs.getInt("GRAPHID"));
			wb.setName(rs.getString("NAME"));
			wb.setProClass(rs.getString("PROCLASS"));
			wb.setStep(rs.getInt("STEP"));
		}
		
		//关闭连接
		DBUtils.close(conn, ps, rs);
		return wb;
	}

}
